package com.seafood.repository;

import com.seafood.entity.Product;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.util.List;

/**
 * 产品数据访问层
 */
@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
    
    /**
     * 根据分类ID查找产品
     */
    Page<Product> findByCategoryIdAndStatus(Long categoryId, Product.ProductStatus status, Pageable pageable);
    
    /**
     * 根据状态查找产品
     */
    Page<Product> findByStatus(Product.ProductStatus status, Pageable pageable);
    
    /**
     * 全文搜索产品（使用MySQL全文索引）
     */
    @Query(value = "SELECT * FROM products p WHERE " +
           "MATCH(p.name, p.description) AGAINST(:keyword IN NATURAL LANGUAGE MODE) " +
           "AND p.status = :status " +
           "ORDER BY MATCH(p.name, p.description) AGAINST(:keyword IN NATURAL LANGUAGE MODE) DESC",
           nativeQuery = true)
    List<Product> searchByKeyword(@Param("keyword") String keyword, @Param("status") String status);
    
    /**
     * 根据关键词模糊搜索（备用搜索方式）
     */
    @Query("SELECT p FROM Product p WHERE " +
           "(p.name LIKE %:keyword% OR p.description LIKE %:keyword% OR " +
           "p.origin LIKE %:keyword% OR p.species LIKE %:keyword%) " +
           "AND p.status = :status")
    Page<Product> findByKeywordContaining(@Param("keyword") String keyword, 
                                         @Param("status") Product.ProductStatus status, 
                                         Pageable pageable);
    
    /**
     * 按价格区间查询产品
     */
    Page<Product> findByPriceBetweenAndStatus(BigDecimal minPrice, BigDecimal maxPrice, 
                                            Product.ProductStatus status, Pageable pageable);
    
    /**
     * 根据产地查询产品
     */
    Page<Product> findByOriginAndStatus(String origin, Product.ProductStatus status, Pageable pageable);
    
    /**
     * 根据等级查询产品
     */
    Page<Product> findByGradeAndStatus(String grade, Product.ProductStatus status, Pageable pageable);
    
    /**
     * 查询热门产品（根据销量排序，这里先按创建时间排序）
     */
    @Query("SELECT p FROM Product p WHERE p.status = :status ORDER BY p.createdAt DESC")
    List<Product> findHotProducts(@Param("status") Product.ProductStatus status, Pageable pageable);
    
    /**
     * 查询推荐产品
     */
    @Query("SELECT p FROM Product p WHERE p.status = :status AND p.stockQuantity > 0 ORDER BY p.price DESC")
    List<Product> findRecommendedProducts(@Param("status") Product.ProductStatus status, Pageable pageable);
    
    /**
     * 检查产品库存
     */
    @Query("SELECT p FROM Product p WHERE p.stockQuantity <= :threshold AND p.status = :status")
    List<Product> findLowStockProducts(@Param("threshold") Integer threshold, @Param("status") Product.ProductStatus status);
    
    /**
     * 复合条件查询
     */
    @Query("SELECT p FROM Product p WHERE " +
           "(:categoryId IS NULL OR p.category.id = :categoryId) AND " +
           "(:keyword IS NULL OR p.name LIKE %:keyword% OR p.description LIKE %:keyword%) AND " +
           "(:origin IS NULL OR p.origin = :origin) AND " +
           "(:minPrice IS NULL OR p.price >= :minPrice) AND " +
           "(:maxPrice IS NULL OR p.price <= :maxPrice) AND " +
           "p.status = :status")
    Page<Product> findByMultipleConditions(@Param("categoryId") Long categoryId,
                                          @Param("keyword") String keyword,
                                          @Param("origin") String origin,
                                          @Param("minPrice") BigDecimal minPrice,
                                          @Param("maxPrice") BigDecimal maxPrice,
                                          @Param("status") Product.ProductStatus status,
                                          Pageable pageable);
}